﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Data;
using System.Collections;
using IronOne.SalesOne.BusinessObjects;
using System.Transactions;

namespace IronOne.SalesOne.DataObjects
{
    public class LinqActivityPlan
    {
        /*public DataTable GetActivityPlan(DateTime fromDate, DateTime toDate, string SOCode)
        {
            return getActivities(fromDate, toDate, SOCode);
                             
        }*/

        public DataTable GetActivityPlan(DateTime fromDate, DateTime toDate, string SOCode)
        {
            /*fromDate = DateTime.Now;
            toDate = DateTime.Now.AddDays(30);
            SOCode = "1212";*/

            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("Date");
                dt.Columns.Add("Lapse");
                dt.Columns.Add("Grace");
                dt.Columns.Add("Renewal");
                dt.Columns.Add("Prospect");
                dt.Columns.Add("Remark");


                var queryPolicy = from plan in db.SalesActivityPolicyEntities

                                  join policy in db.PolicyEntities
                                  on plan.ContractId equals policy.ContractId

                                  from state in db.PolicyStatusEntities

                                  where fromDate <= plan.ApptDate
                                  && plan.ApptDate <= toDate
                                  && plan.SOCode == SOCode
                                  select new
                                  {
                                      plan.ContractId,
                                      ApptDate = plan.ApptDate,
                                      Status = policy.PolicyState == 1 ? "lapse" : (policy.DueDate > DateTime.Now ? "renewal" : "grace")
                                      //Status = policy.PolicyState == "lapse" ? "lapse" : policy.DueDate > DateTime.Now ? "renewal" : "grace"
                                  };
                

                var queryRemark = from remark in db.SalesActivityRemarkEntities
                                  where fromDate <= remark.Date
                                    && remark.Date <= toDate
                                    && remark.SOCode == SOCode
                                  select new
                                  {
                                      remark.Date,
                                      remark.Remark
                                  };

                var queryProspect = from prospect in db.SalesActivityProspectEntities
                                    where fromDate <= prospect.ApptDate
                                    && prospect.ApptDate <= toDate
                                    && prospect.SOCode == SOCode
                                    join pros in db.ProspectEntities on prospect.ProspectId equals pros.ProspectID 
                                    select new
                                    {
                                        prospect.ApptDate,
                                        customerId = pros.CustomerID,
                                        prospect.ProspectId
                                    };

                DateTime date = fromDate;
                
                while (date <= toDate)
                {
                    DataRow row = dt.NewRow();
                    row["Date"] = date.ToShortDateString();

                    fillDataCell(SOCode,row, "Renewal", queryPolicy.Where(p => p.ApptDate.Date == date.Date && p.Status == "renewal").Select(p => p.ContractId).Distinct().GetEnumerator());
                    fillDataCell(SOCode, row, "Grace", queryPolicy.Where(p => p.ApptDate.Date == date.Date && p.Status == "grace").Select(p => p.ContractId).Distinct().GetEnumerator());
                    fillDataCell(SOCode, row, "Lapse", queryPolicy.Where(p => p.ApptDate.Date == date.Date && p.Status == "lapse").Select(p => p.ContractId).Distinct().GetEnumerator());
                    fillDataCell(SOCode, row, "Prospect", queryProspect.Where(p => p.ApptDate.Date == date.Date).Select(p => p.customerId + "," + p.ProspectId).Distinct().GetEnumerator());

                    var remark = queryRemark.SingleOrDefault(p => p.Date.Date == date.Date);
                    if(remark != null)
                        row["Remark"] = string.IsNullOrEmpty(remark.Remark) ? " " : remark.Remark;

                    dt.Rows.Add(row);
                    date = date.AddDays(1);                    
                }

                return dt;
            }     
                 
        }

        private void fillDataCell(string socode, DataRow row, string field, IEnumerator iEnumerator)
        {
            while (iEnumerator.MoveNext())
            {
                if (!string.IsNullOrEmpty(row[field].ToString()))
                    row[field] += "</br>";

                if (field == "Prospect")
                    row[field] = row[field] + "<a href='../ContactManagement/ProspectDetails.aspx?plan=true&type=" + field + "&date=" + row["Date"] + "&id=" + iEnumerator.Current.ToString().Split(',')[0] + "'>" + iEnumerator.Current.ToString().Split(',')[1] + "</a>";                
                else
                    row[field] = row[field] + "<a href='../SalesActivity/AppointmentDetails.aspx?type=" + field + "&socode=" + socode + "&date=" + row["Date"] + "&id=" + iEnumerator.Current + "'>" + iEnumerator.Current + "</a>";                
            }
        }


        public SalesAppoinments GetActivity(string contractId, string socode, DateTime date)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToActivityObject(db.SalesActivityPolicyEntities
                            .SingleOrDefault(p => p.ContractId == contractId && p.ApptDate == date && p.SOCode == socode));
            }
        }

       


        public void ClearSupervisorRemark(DateTime date, string soCode)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                SalesActivityRemarkEntity remark = db.SalesActivityRemarkEntities.SingleOrDefault(p => p.Date.Date == date.Date && p.SOCode == soCode);
                if(remark != null)
                    db.SalesActivityRemarkEntities.DeleteOnSubmit(remark);
                db.SubmitChanges(); 
            }
        }

        public ActivityRemark GetRemarkForSO(string soCode, DateTime date)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                /*var query = db.SalesActivityRemarkEntities.SingleOrDefault(p => p.SOCode == soCode && p.Date.Date == date.Date);
                return query != null ? query.Remark : string.Empty;*/
                return Mapper.ToActivityRemarkObject(db.SalesActivityRemarkEntities.SingleOrDefault(p => p.SOCode == soCode && p.Date.Date == date.Date));
            }
        }

        public void InsertOrUpdateRemark(List<ActivityRemark> updateList, List<ActivityRemark> insertList)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                IEnumerator remarks = insertList.GetEnumerator();
                while (remarks.MoveNext())
                {
                    InsertSupervisorRemark((ActivityRemark)remarks.Current,db);                    
                }

                remarks = updateList.GetEnumerator();
                while (remarks.MoveNext())
                {
                    UpdateSupervisorRemark((ActivityRemark)remarks.Current,db);
                }
                //scope.Complete();
                db.SubmitChanges();
            }
        }

        private int UpdateSupervisorRemark(ActivityRemark activityRemark,SODataDataContext db)
        {
            /*using (SODataDataContext db = DataContextFactory.CreateContext())
            {*/
                try
                {
                    SalesActivityRemarkEntity entity = Mapper.ToActivityRemarkEntity(activityRemark);
                    db.SalesActivityRemarkEntities.Attach(entity, true);
                    //db.SubmitChanges();
                    return 1;
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    return 0;
                }
            //}
        }

        private void InsertSupervisorRemark(ActivityRemark activityRemark, SODataDataContext db)
        {            
            /*using (SODataDataContext db = DataContextFactory.CreateContext())
            {*/
                try
                {
                    SalesActivityRemarkEntity entity = Mapper.ToActivityRemarkEntity(activityRemark);
                    db.SalesActivityRemarkEntities.InsertOnSubmit(entity);
                    //db.SubmitChanges();                    
                }
                catch (ChangeConflictException)
                {
                    throw new Exception("A change to contact record was made before your changes.");
                }
                catch (Exception)
                {
                    throw new Exception("Unable to Insert");
                }
            //}
        }

        public ActivityRemark GetRemarkForProspect(string soCode, DateTime date)
        {
            using (SODataDataContext db = DataContextFactory.CreateContext())
            {
                return Mapper.ToActivityRemarkObject(db.SalesActivityProspectEntities
                            .SingleOrDefault(p => p.SOCode == soCode && p.ApptDate == date));
            }
        }
    }
}
